import pymysql

class DataBase():   #类名和模块名一致，robot导入时不用写类名@@@@@
    def __init__(self,mysql_name):
        try:
            self.conn = pymysql.connect(host='127.0.0.1',
                                port=3306,
                                user='root',
                                password='admin',
                                db=mysql_name,
                                charset='utf8' )
        except pymysql.err.OperationalError as e:
            print(e)

    def insert_one(self, insert_sql,record):
        # 使用cursor()方法获取操作游标
        cur = self.conn.cursor()
        try:
            #示例
            # insertSql = 'INSERT INTO users(username, userpass, nickname) VALUES(%s, %s, %s) '
            # rows = cur.execute(insertSql, ("shuke", "123", "舒克"))
            #返回插入的行数
            cur.execute(insert_sql,record)
            # 提交
            self.conn.commit()
        except Exception as e:
            # 错误回滚
            self.conn.rollback()


    def update_one(self,update_sql,record):

        cur = self.conn.cursor()
        try:
            cur.execute(update_sql,record)  # 执行sql语句
            # 提交
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()

    #批量插入sql  1000条插入一次
    def batch_insert(self,insertSql,records):
        #每次插入的最大长度
        MAX_LEN = 1000
        cur = self.conn.cursor()
        try:
            # insertSql = 'INSERT INTO users(username, userpass, nickname, age) VALUES(%s, %s, %s, %s)'
            # records = [("member1", "123", "会员1", 12),
            #         ("member2", "123", "会员2", 34),
            #         ("member3", "123", "会员3", 23),
            #         ("member4", "123", "会员4", 42)]
            for i in range(0,len(records),MAX_LEN):
                cur.executemany(insertSql, records[i:i+MAX_LEN])
            # 提交
            self.conn.commit()
        except Exception as e:
            print(e)
            self.conn.rollback()

    def select_list(self, select_sql,condition=None):
        # 使用cursor()方法获取操作游标
        cur = self.conn.cursor()
        try:
            cur.execute(select_sql,condition)  # 执行sql语句
            results = cur.fetchall()  # 获取查询的所有记录
            return results
        except Exception as e:
            self.conn.rollback()

    def close_conn(self):
        self.conn.close()